In [1]:
import pandas as pd
import matplotlib.pyplot as plt
from sqlalchemy import create_engine
import geopandas as gpd
import psycopg2
from psycopg2 import OperationalError
In [2]:
def create_connection():
    try:
        connection = psycopg2.connect(
            dbname='graduates_db',  # База данных
            user='postgres',  # Имя пользователя PostgreSQL
            password='12345678',  # Пароль
            host='postgres',        # Адрес PostgreSQL
            port='5432'
        )
        if connection.closed == 0:
            print("Connected to PostgreSQL database.")
        return connection
    except OperationalError as e:
        print("Error while connecting to PostgreSQL")
        print(f"Error: '{e}'")
        return None
In [3]:
def fetch_data(query):
    engine = create_connection()
    df = pd.read_sql(query, con=engine)
    return df
In [4]:
# Средняя зп по регионам
def plot_salary_by_region_and_education():
    query = """
    SELECT r.object_name AS region, 
        g.education_level, 
        AVG(g.average_salary) AS avg_salary,
        SUM(g.count_graduate * g.percent_employed / 100) AS total_employed
    FROM Graduates g
    JOIN Regions r ON g.region_id = r.region_id
    WHERE g.average_salary IS NOT NULL AND r.object_name != 'Российская Федерация' AND r.object_name NOT LIKE '%федеральный округ'
    GROUP BY r.object_name, g.education_level
    ORDER BY region;
    """
    df = fetch_data(query)

    if df is not None:
        education_levels = ['Бакалавриат, специалитет', 'Магистратура', 'СПО: специалисты среднего звена', 'СПО: квалифицированные рабочие и служащие']

        # Отфильтруем регионы по средней зарплате магистратуры и отсортируем
        magister_df = df[df['education_level'] == 'Магистратура'].sort_values(by='avg_salary', ascending=False)
        sorted_regions = magister_df['region'].values

         # Подготовим словарь регионов с количеством трудоустроенных для отображения
        total_employed_by_region = df.groupby('region')['total_employed'].sum().to_dict()

        # Обновим названия регионов с добавлением количества трудоустроенных
        labeled_regions = [
            f"{region} ({int(total_employed_by_region[region])} чел.)" if region in total_employed_by_region else region
            for region in sorted_regions
        ]

        fig, ax = plt.subplots(figsize=(18, 8))

        # Построение линии для каждого уровня образования
        for level in education_levels:
            df_level = df[df['education_level'] == level].set_index('region').reindex(sorted_regions)
            avg_salary = df_level['avg_salary']
            ax.plot(labeled_regions, avg_salary, marker='o', label=level)

        ax.set_xlabel('Регион')
        ax.set_ylabel('Средняя зарплата')
        ax.set_title('Средняя зарплата выпускников по регионам и уровням образования')
        ax.legend(title="Уровень образования")
        ax.tick_params(axis='x', rotation=90)
        plt.xticks(rotation=45, ha='right')
        plt.grid()
        plt.tight_layout()
        plt.show()

plot_salary_by_region_and_education()
Connected to PostgreSQL database.
/tmp/ipykernel_13/4254846913.py:3: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  df = pd.read_sql(query, con=engine)
No description has been provided for this image
In [5]:
def plot_employment_rate_with_counts():
    query = """
    SELECT 
        r.object_name AS region, 
        g.education_level, 
        AVG(g.percent_employed) AS avg_employment_rate,
        SUM(g.count_graduate * g.percent_employed / 100) AS total_employed
    FROM Graduates g
    JOIN Regions r ON g.region_id = r.region_id
    WHERE g.percent_employed IS NOT NULL AND g.count_graduate IS NOT NULL 
          AND r.object_name != 'Российская Федерация' AND r.object_name NOT LIKE '%федеральный округ'
    GROUP BY r.object_name, g.education_level
    ORDER BY region;
    """
    df = fetch_data(query)

    if df is not None:
        education_levels = ['Бакалавриат, специалитет', 'Магистратура', 'СПО: специалисты среднего звена', 'СПО: квалифицированные рабочие и служащие']

        # Отфильтруем регионы по проценту трудоустроенных магистратуры и отсортируем
        magister_df = df[df['education_level'] == 'Магистратура'].sort_values(by='avg_employment_rate', ascending=False)
        sorted_regions = magister_df['region'].values

        # Подготовим словарь регионов с количеством трудоустроенных для отображения
        total_employed_by_region = df.groupby('region')['total_employed'].sum().to_dict()

        # Обновим названия регионов с добавлением количества трудоустроенных
        labeled_regions = [
            f"{region} ({int(total_employed_by_region[region])} чел.)" if region in total_employed_by_region else region
            for region in sorted_regions
        ]

        fig, ax = plt.subplots(figsize=(18, 8))

        # Построение линии для каждого уровня образования
        for level in education_levels:
            df_level = df[df['education_level'] == level].set_index('region').reindex(sorted_regions)
            avg_employment_rate = df_level['avg_employment_rate']
            ax.plot(labeled_regions, avg_employment_rate, marker='o', label=level)

        ax.set_xlabel('Регион (количество трудоустроенных)')
        ax.set_ylabel('Средний процент трудоустроенных (%)')
        ax.set_title('Средний процент трудоустроенных выпускников по регионам и уровням образования')
        ax.legend(title="Уровень образования")
        ax.tick_params(axis='x', rotation=90)
        plt.xticks(rotation=45, ha='right')
        plt.grid()
        plt.tight_layout()
        plt.show()

plot_employment_rate_with_counts()
Connected to PostgreSQL database.
/tmp/ipykernel_13/4254846913.py:3: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  df = pd.read_sql(query, con=engine)
No description has been provided for this image
In [6]:
def plot_graduates_by_year_education():
    query = """
    SELECT g.year, g.education_level, SUM(g.count_graduate) AS total_graduates
    FROM Graduates g
    WHERE g.year BETWEEN 2019 AND 2023
    GROUP BY g.year, g.education_level
    ORDER BY g.year;
    """
    df = fetch_data(query)

    if df is not None:
        # Определение уровней образования и установка параметров для 2x2 сетки графиков
        education_levels = ["Бакалавриат, специалитет", "Магистратура", "СПО: специалисты среднего звена", "СПО: квалифицированные рабочие и служащие"]
        fig, axes = plt.subplots(2, 2, figsize=(14, 10))

        # Находим максимальное значение для общей настройки y-оси
        max_value = df['total_graduates'].max()

        for idx, (level, ax) in enumerate(zip(education_levels, axes.flatten())):
            df_level = df[df['education_level'] == level]
            
            # Фильтрация для значений за каждый год
            df_level = df_level[df_level['year'].isin([2019, 2020, 2021, 2022, 2023])]

            # Построение столбчатой диаграммы
            ax.bar(df_level['year'], df_level['total_graduates'], color='red')
            ax.set_title(f"Количество выпускников {level}")
            ax.set_xlabel('Год')
            ax.set_ylabel('Количество выпускников')
            ax.set_xticks([2019, 2020, 2021, 2022, 2023])
            ax.set_ylim(0, max_value)  # Установка общего масштаба y-оси

        plt.tight_layout()
        plt.show()

plot_graduates_by_year_education()
Connected to PostgreSQL database.
/tmp/ipykernel_13/4254846913.py:3: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  df = pd.read_sql(query, con=engine)
No description has been provided for this image
In [7]:
from shapely.geometry import Polygon, MultiPolygon

def shift_longitude(geometry):
    """
    Функция для преобразования отрицательных долгот в положительные, чтобы устранить разрыв карты.
    """
    if geometry.bounds[0] < -150:  # Проверяем, есть ли отрицательные координаты долготы
        new_coords = []
        for polygon in geometry.geoms:
            shifted_coords = [
                [(x + 360 if x < 0 else x), y] for x, y in polygon.exterior.coords
            ]
            new_coords.append(Polygon(shifted_coords))
        return MultiPolygon(new_coords)
    return geometry
In [8]:
#Общее количество выпускников магистратуры по регионам
def plot_graduates_heatmap():
    # Загрузка данных о выпускниках
    query = """
    SELECT r.object_name AS region, SUM(g.count_graduate) AS total_graduates
    FROM Graduates g
    JOIN Regions r ON g.region_id = r.region_id
    WHERE g.education_level = 'Магистратура' AND r.object_name != 'Российская Федерация' AND r.object_name NOT LIKE '%федеральный округ'
    GROUP BY region
    ORDER BY SUM(g.count_graduate);
    """
    graduates_df = fetch_data(query)
    # Загрузка данных о географии России
    russia_geo = gpd.read_file("russia_regions/russia_regions.geojson")

    # Объединение данных о выпускниках с географическими данными
    russia_geo = russia_geo.merge(graduates_df, left_on="region", right_on="region", how="left")
    russia_geo["total_graduates"] = russia_geo["total_graduates"].fillna(0)
    print(russia_geo[["region", "total_graduates"]].sort_values(by="total_graduates", ascending=False).head(10))

    # Преобразование долгот для правильного отображения на карте
    russia_geo["geometry"] = russia_geo["geometry"].apply(shift_longitude)

    # Найти третье по величине значение
    top_values = russia_geo["total_graduates"].nlargest(3)
    third_max_graduates = top_values.iloc[2]  # Третье по величине значение

    # Построение тепловой карты
    fig, ax = plt.subplots(1, 1, figsize=(20, 10))
    russia_geo.plot(
        column="total_graduates",
        cmap="GnBu",
        linewidth=0.1,
        ax=ax,
        edgecolor="0.8",
        legend=True,
        vmin=0,
        vmax=third_max_graduates
    )
    
    # Добавление уникальных цветов для Москвы и Санкт-Петербурга
    for idx, row in russia_geo.iterrows():
        centroid = row["geometry"].centroid
        if row["region"] == "Москва" or row["region"] == "Санкт-Петербург":
            ax.text(centroid.x, centroid.y, int(row["total_graduates"]),
                    ha="center", va="center", fontsize=7, color="black", weight="bold")
    
    ax.set_title("Общее количество выпускников магистратуры по регионам")
    ax.axis("off")  # Убираем оси для чистоты визуализации
    plt.show()

plot_graduates_heatmap()
Connected to PostgreSQL database.
/tmp/ipykernel_13/4254846913.py:3: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  df = pd.read_sql(query, con=engine)
                     region  total_graduates
24                   Москва         397202.0
22          Санкт-Петербург         139818.0
38       Ростовская область          64712.0
55     Республика Татарстан          61492.0
42       Краснодарский край          45150.0
16  Республика Башкортостан          39430.0
70     Свердловская область          38998.0
13      Воронежская область          35644.0
62    Нижегородская область          35512.0
1       Саратовская область          31138.0
No description has been provided for this image
In [9]:
#Общее количество выпускников бакалавриата, специалитета по регионам
def plot_graduates_heatmap_bachelor():
    # Загрузка данных о выпускниках
    query = """
    SELECT r.object_name AS region, SUM(g.count_graduate) AS total_graduates
    FROM Graduates g
    JOIN Regions r ON g.region_id = r.region_id
    WHERE g.education_level = 'Бакалавриат, специалитет' AND r.object_name != 'Российская Федерация' AND r.object_name NOT LIKE '%федеральный округ'
    GROUP BY region
    ORDER BY SUM(g.count_graduate);
    """
    graduates_df = fetch_data(query)
    # Загрузка данных о географии России
    russia_geo = gpd.read_file("russia_regions/russia_regions.geojson")

    # Объединение данных о выпускниках с географическими данными
    russia_geo = russia_geo.merge(graduates_df, left_on="region", right_on="region", how="left")
    russia_geo["total_graduates"] = russia_geo["total_graduates"].fillna(0)
    print(russia_geo[["region", "total_graduates"]].sort_values(by="total_graduates", ascending=False).head(10))

    # Преобразование долгот для правильного отображения на карте
    russia_geo["geometry"] = russia_geo["geometry"].apply(shift_longitude)

    # Найти третье по величине значение
    top_values = russia_geo["total_graduates"].nlargest(3)
    third_max_graduates = top_values.iloc[2]  # Третье по величине значение

    # Построение тепловой карты
    fig, ax = plt.subplots(1, 1, figsize=(20, 10))
    russia_geo.plot(
        column="total_graduates",
        cmap="GnBu",
        linewidth=0.1,
        ax=ax,
        edgecolor="0.8",
        legend=True,
        vmin=0,
        vmax=third_max_graduates
    )
    
    # Добавление уникальных цветов для Москвы и Санкт-Петербурга
    for idx, row in russia_geo.iterrows():
        centroid = row["geometry"].centroid
        if row["region"] == "Москва" or row["region"] == "Санкт-Петербург":
            ax.text(centroid.x, centroid.y, int(row["total_graduates"]),
                    ha="center", va="center", fontsize=7, color="black", weight="bold")
    
    ax.set_title("Общее количество выпускников бакалавриата, специалитета по регионам")
    ax.axis("off")  # Убираем оси для чистоты визуализации
    plt.show()

plot_graduates_heatmap_bachelor()
Connected to PostgreSQL database.
/tmp/ipykernel_13/4254846913.py:3: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  df = pd.read_sql(query, con=engine)
                     region  total_graduates
24                   Москва        1309304.0
22          Санкт-Петербург         456448.0
55     Республика Татарстан         225642.0
38       Ростовская область         196146.0
70     Свердловская область         180322.0
42       Краснодарский край         167078.0
16  Республика Башкортостан         148156.0
74      Челябинская область         139818.0
39        Самарская область         135036.0
79    Новосибирская область         132138.0
No description has been provided for this image
In [10]:
def plot_mean_employment_by_study_area():
    query = """
    SELECT sa.study_area_name AS study_area, g.percent_employed, g.count_graduate
    FROM Graduates g
    JOIN Study_Areas sa ON g.study_area_id = sa.study_area_id;
    """
    df = fetch_data(query)

    if df is not None:
        # Рассчитываем средний процент трудоустроенных и общее количество выпускников по областям
        mean_employment = df.groupby('study_area').agg(
            percent_employed=('percent_employed', 'mean'),
            total_graduates=('count_graduate', 'sum')
        ).reset_index()
        mean_employment = mean_employment.sort_values(by='percent_employed', ascending=False)

        # Подписи с количеством выпускников
        mean_employment['study_area_label'] = mean_employment.apply(
            lambda row: f"{row['study_area']} ({row['total_graduates']:,} чел.)".replace(',', ' '), axis=1
        )

        # Построение графика
        plt.figure(figsize=(16, 10))
        plt.plot(mean_employment['study_area_label'], mean_employment['percent_employed'], marker='o', linestyle='-', color='r')
        plt.xlabel('Область образования (Количество выпускников)')
        plt.ylabel('Процент трудоустроенных')
        plt.title('Процент трудоустройства по областям образования')
        plt.xticks(rotation=45, ha='right')
        plt.ylim(0, mean_employment['percent_employed'].max() + 5)
        
        # Добавление значений процентов над каждой точкой
        for i, percent in enumerate(mean_employment['percent_employed']):
            plt.text(i, percent + 3, f'{percent:.1f}', ha='center', va='bottom', fontsize=12, color='black')
        
        plt.tight_layout()
        plt.grid()
        plt.show()

plot_mean_employment_by_study_area()
Connected to PostgreSQL database.
/tmp/ipykernel_13/4254846913.py:3: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  df = pd.read_sql(query, con=engine)
No description has been provided for this image
In [11]:
def plot_mean_employment_by_specialty_section():
    query = """
    SELECT s.specialty_section AS specialty_section, g.percent_employed, g.count_graduate
    FROM Graduates g
    JOIN Specialties s ON g.specialty_id = s.specialty_id;
    """
    df = fetch_data(query)

    if df is not None:
        # Рассчитываем средний процент трудоустроенных и общее количество выпускников по укрупненным группам специальностей
        mean_employment = df.groupby('specialty_section').agg(
            percent_employed=('percent_employed', 'mean'),
            total_graduates=('count_graduate', 'sum')
        ).reset_index()
        mean_employment = mean_employment.sort_values(by='percent_employed', ascending=False)

        # Формируем метки для укрупненных групп с форматированием количества выпускников
        mean_employment['specialty_section_label'] = mean_employment.apply(
            lambda row: f"{row['specialty_section']} ({row['total_graduates']:,} чел.)".replace(',', ' '), axis=1
        )

        # Построение графика
        plt.figure(figsize=(16, 10))
        plt.plot(mean_employment['specialty_section_label'], mean_employment['percent_employed'], marker='o', linestyle='-', color='g')
        plt.xlabel('Укрупненная группа специальностей (Количество выпускников)')
        plt.ylabel('Процент трудоустроенных')
        plt.title('Процент трудоустройства по укрупненным группам специальностей')
        
        # Настройка меток, сетки и поворота текста
        plt.xticks(rotation=45, ha='right')
        plt.ylim(0, mean_employment['percent_employed'].max() + 5)
        plt.yticks(range(0, 101, 10))
        plt.grid(visible=True, which='both', axis='both', linestyle='--', linewidth=0.5)

        # Добавление значений процентов над каждой точкой
        for i, percent in enumerate(mean_employment['percent_employed']):
            plt.text(i, percent + 3, f'{percent:.1f}', ha='center', va='bottom', fontsize=8, color='black')

        plt.tight_layout()
        plt.margins(0)
        plt.show()

plot_mean_employment_by_specialty_section()
Connected to PostgreSQL database.
/tmp/ipykernel_13/4254846913.py:3: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  df = pd.read_sql(query, con=engine)
No description has been provided for this image
In [12]:
def plot_employment_median_by_specialty():
    query = """
    SELECT s.specialty_name AS specialty, g.percent_employed, g.count_graduate
    FROM Graduates g
    JOIN Specialties s ON g.specialty_id = s.specialty_id
    """
    df = fetch_data(query)

    if df is not None:
        # Расчет среднего процента трудоустроенных и общего числа выпускников по специальностям
        mean_df = df.groupby('specialty').agg(
            percent_employed=('percent_employed', 'mean'),
            total_graduates=('count_graduate', 'sum')
        ).reset_index()
        mean_df['employed_graduates'] = (mean_df['percent_employed'] * mean_df['total_graduates'] / 100).round()

        # Сортировка по среднему проценту трудоустроенных
        mean_df = mean_df.sort_values(by='percent_employed', ascending=False)
        
        # Настройка графика
        plt.figure(figsize=(17, len(mean_df) * 0.2))
        
        # Построение столбчатой диаграммы
        colors = ['blue' if specialty == 'Программная инженерия' else 'green' for specialty in mean_df['specialty']]
        bars = plt.barh(mean_df['specialty'], mean_df['percent_employed'], color=colors)
        plt.xlabel('Процент трудоустроенных')
        plt.ylabel('Специальность')
        plt.xlim(0, 100)
        plt.title('Процент трудоустроенных по специальностям')

        # Добавление текста с количеством трудоустроенных внутри столбца и процентом справа от столбца
        for bar, percent, employed in zip(bars, mean_df['percent_employed'], mean_df['employed_graduates']):
            # Количество трудоустроенных внутри столбца
            plt.text(bar.get_width() / 2, bar.get_y() + bar.get_height() / 2,
                     f'{int(employed):,} чел.'.replace(',', ' '),
                     va='center', ha='center', color='white', fontsize=10)
            # Процент трудоустроенных справа от столбца
            plt.text(bar.get_width() + 1, bar.get_y() + bar.get_height() / 2,
                     f'{percent:.1f}%', va='center', ha='left', fontsize=10)

        # Настройка осей
        plt.gca().invert_yaxis()
        plt.tight_layout()
        plt.margins(0)
        plt.show()

plot_employment_median_by_specialty()
Connected to PostgreSQL database.
/tmp/ipykernel_13/4254846913.py:3: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  df = pd.read_sql(query, con=engine)
No description has been provided for this image
In [13]:
def plot_employment_trends_by_education_level():
    # Запрос данных с группировкой по уровню образования и году
    query = """
    SELECT g.education_level AS education_level, g.year AS year, AVG(g.percent_employed) AS avg_percent_employed
    FROM Graduates g
    WHERE g.year BETWEEN 2019 AND 2023
    GROUP BY g.education_level, g.year
    ORDER BY g.education_level, g.year;
    """
    df = fetch_data(query)

    if df is not None:
        # Уникальные уровни образования для построения отдельных линий
        education_levels = df['education_level'].unique()
        
        plt.figure(figsize=(12, 8))
        
        # Построение графика для каждого уровня образования
        for level in education_levels:
            level_data = df[df['education_level'] == level]
            plt.plot(level_data['year'], level_data['avg_percent_employed'], marker='o', label=level)
        
        # Настройки графика
        plt.xlabel('Год')
        plt.ylabel('Средний процент трудоустроенных')
        plt.title('Динамика трудоустройства по уровням образования (2019-2023)')
        plt.legend(title='Уровень образования')
        plt.xticks(range(2019, 2024))
        plt.ylim(0, 100)
        plt.grid(visible=True, which='both', axis='both', linestyle='--', linewidth=0.5)
        
        plt.tight_layout()
        plt.show()

plot_employment_trends_by_education_level()
Connected to PostgreSQL database.
/tmp/ipykernel_13/4254846913.py:3: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  df = pd.read_sql(query, con=engine)
No description has been provided for this image
In [14]:
def plot_employment_trends_for_programming_engineering():
    # Запрос для получения данных по "Программной инженерии"
    query = """
    SELECT g.year AS year, g.education_level AS education_level, AVG(g.percent_employed) AS avg_employment
    FROM Graduates g
    JOIN Specialties s ON g.specialty_id = s.specialty_id
    WHERE s.specialty_name = 'Программная инженерия' 
    AND g.year BETWEEN 2019 AND 2023
    GROUP BY g.year, g.education_level
    ORDER BY g.year, g.education_level;
    """
    
    # Получаем данные из базы данных
    df = fetch_data(query)

    if df is not None:
        # Разделяем данные по уровням образования
        df_bach_spec = df[df['education_level'] == 'Бакалавриат, специалитет']
        df_masters = df[df['education_level'] == 'Магистратура']
        
        # Создание графика
        plt.figure(figsize=(10, 6))

        # Линия для "Бакалавриат, специалитет"
        plt.plot(df_bach_spec['year'], df_bach_spec['avg_employment'], marker='o', color='blue', label='Бакалавриат, специалитет')

        # Линия для "Магистратура"
        plt.plot(df_masters['year'], df_masters['avg_employment'], marker='o', color='green', label='Магистратура')

        # Настройка графика
        plt.xlabel('Год')
        plt.ylabel('Средний процент трудоустроенных')
        plt.title('Процент трудоустройства на специальности "Программная инженерия" (2019-2023)')
        plt.ylim(0, 100)
        plt.grid(visible=True, linestyle='--', linewidth=0.5)

        # Добавление легенды
        plt.legend(title="Уровень образования")
        plt.xticks(range(2019, 2024))

        # Подгонка макета и отображение графика
        plt.tight_layout()
        plt.show()

# Вызов функции для построения графика
plot_employment_trends_for_programming_engineering()
Connected to PostgreSQL database.
/tmp/ipykernel_13/4254846913.py:3: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  df = pd.read_sql(query, con=engine)
No description has been provided for this image
In [15]:
def plot_graduates_by_study_area():
    query = """
    SELECT sa.study_area_name, SUM(g.count_graduate) AS total_graduates
    FROM Graduates g
    JOIN Study_Areas sa ON g.study_area_id = sa.study_area_id
    WHERE g.count_graduate IS NOT NULL
    GROUP BY sa.study_area_name
    ORDER BY total_graduates DESC;
    """
    df = fetch_data(query)

    if df is not None and not df.empty:
        # Сортировка данных по количеству выпускников
        df = df.sort_values(by="total_graduates", ascending=True)

        # Построение графика
        fig, ax = plt.subplots(figsize=(18, 8))
        ax.plot(df["study_area_name"], df["total_graduates"], marker="o", linestyle="-", color="blue")

        # Настройки осей и подписи
        ax.set_xlabel("Укрупнённая группа специальностей (study_area_name)")
        ax.set_ylabel("Количество выпускников")
        ax.set_title("Количество выпускников по укрупнённым группам специальностей")
        plt.xticks(rotation=45, ha="right")
        plt.grid()
        plt.tight_layout()
        plt.show()
    else:
        print("Нет данных для построения графика.")

plot_graduates_by_study_area()
Connected to PostgreSQL database.
/tmp/ipykernel_13/4254846913.py:3: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  df = pd.read_sql(query, con=engine)
No description has been provided for this image
In [16]:
def plot_graduates_by_specialty_section():
    query = """
    SELECT s.specialty_section AS specialty_section, SUM(g.count_graduate) AS total_graduates
    FROM Graduates g
    JOIN Specialties s ON g.specialty_id = s.specialty_id
    GROUP BY s.specialty_section
    ORDER BY total_graduates DESC;
    """
    df = fetch_data(query)

    if df is not None:
        # Сортировка данных по количеству выпускников
        df = df.sort_values(by="total_graduates", ascending=True)

        # Формируем метки для разделов специальностей с форматированием количества выпускников
        df['specialty_section_label'] = df.apply(
            lambda row: f"{row['specialty_section']} ({row['total_graduates']:,} чел.)".replace(',', ' '), axis=1
        )

        # Построение графика
        plt.figure(figsize=(16, 10))
        plt.plot(df['specialty_section_label'], df['total_graduates'], marker='o', linestyle='-', color='b')
        plt.xlabel('Разделы специальностей (Количество выпускников)')
        plt.ylabel('Количество выпускников')
        plt.title('Количество выпускников по разделам специальностей')

        # Настройка меток, сетки и поворота текста
        plt.xticks(rotation=45, ha='right')
        plt.grid(visible=True, which='both', axis='both', linestyle='--', linewidth=0.5)

        plt.tight_layout()
        plt.margins(0)
        plt.show()
    else:
        print("Нет данных для построения графика.")

plot_graduates_by_specialty_section()
Connected to PostgreSQL database.
/tmp/ipykernel_13/4254846913.py:3: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  df = pd.read_sql(query, con=engine)
No description has been provided for this image
In [17]:
def plot_graduates_by_specialty():
    query = """
    SELECT s.specialty_name AS specialty, SUM(g.count_graduate) AS total_graduates
    FROM Graduates g
    JOIN Specialties s ON g.specialty_id = s.specialty_id
    GROUP BY s.specialty_name
    ORDER BY total_graduates DESC;
    """
    df = fetch_data(query)

    if df is not None:
        # Сортировка данных по количеству выпускников в порядке убывания
        df = df.sort_values(by="total_graduates", ascending=False)

        # Настройка графика
        plt.figure(figsize=(17, len(df) * 0.2))

        # Построение горизонтальной столбчатой диаграммы
        bars = plt.barh(df['specialty'], df['total_graduates'], color='green')
        plt.xlabel('Количество выпускников')
        plt.ylabel('Специальность')
        plt.title('Количество выпускников по специальностям')

        # Определяем максимальную ширину столбца
        max_width = max(bar.get_width() for bar in bars)

        # Добавление текста с количеством выпускников внутри столбца
        for bar, total_graduates in zip(bars, df['total_graduates']):
            plt.text(max_width / 2,  # Текст всегда по центру самого длинного столбца
                     bar.get_y() + bar.get_height() / 2,  # Центр по вертикали
                     f'{int(total_graduates):,} чел.'.replace(',', ' '),
                     va='center', ha='center', color='black', fontsize=10)

        # Настройка осей
        plt.gca().invert_yaxis()
        plt.tight_layout()
        plt.margins(0)
        plt.show()
    else:
        print("Нет данных для построения графика.")

plot_graduates_by_specialty()
Connected to PostgreSQL database.
/tmp/ipykernel_13/4254846913.py:3: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  df = pd.read_sql(query, con=engine)
No description has been provided for this image
In [18]:
def plot_average_salary_by_specialty():
    query = """
    SELECT s.specialty_name AS specialty, g.average_salary, g.count_graduate
    FROM Graduates g
    JOIN Specialties s ON g.specialty_id = s.specialty_id
    WHERE g.average_salary IS NOT NULL;
    """
    df = fetch_data(query)

    if df is not None:
        # Рассчитываем среднюю зарплату и общее количество выпускников по каждой специальности
        df = df.groupby('specialty').agg(
            average_salary=('average_salary', 'mean'),  # Средняя зарплата
            total_graduates=('count_graduate', 'sum')   # Сумма выпускников
        ).reset_index()

        # Исключаем специальности с полностью NULL значениями (проверка уже сделана в WHERE)
        df = df.dropna(subset=['average_salary'])

        # Сортировка данных по средней зарплате в порядке убывания
        df = df.sort_values(by="average_salary", ascending=False)

        # Настройка графика
        plt.figure(figsize=(17, len(df) * 0.2))

        # Построение горизонтальной столбчатой диаграммы с выделением "Программной инженерии"
        bars = plt.barh(
            df['specialty'],
            df['average_salary'],
            color=[
                '#90EE90' if specialty == "Программная инженерия" else '#ADD8E6'  # Светло-зеленый и светло-голубой
                for specialty in df['specialty']
            ]
        )
        plt.xlabel('Средняя зарплата (руб.)')
        plt.ylabel('Специальность')
        plt.title('Средняя зарплата по специальностям')

        # Определяем максимальную ширину столбца
        max_width = max(bar.get_width() for bar in bars)

        # Добавление текста с зарплатой и количеством выпускников внутри столбца
        for bar, avg_salary, total_graduates in zip(bars, df['average_salary'], df['total_graduates']):
            plt.text(max_width / 2,  # Текст всегда по центру самого длинного столбца
                     bar.get_y() + bar.get_height() / 2,  # Центр по вертикали
                     f'{int(avg_salary):,} руб. ({int(total_graduates):,} чел.)'.replace(',', ' '),
                     va='center', ha='center', color='black', fontsize=10)  # Черный цвет текста

        # Настройка осей
        plt.gca().invert_yaxis()
        plt.tight_layout()
        plt.margins(0)
        plt.show()
    else:
        print("Нет данных для построения графика.")

plot_average_salary_by_specialty()
Connected to PostgreSQL database.
/tmp/ipykernel_13/4254846913.py:3: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.
  df = pd.read_sql(query, con=engine)
No description has been provided for this image